Information is conveniently stored in repositories such as databases. Stored information is accessed and updated with applications executing in a database management system (DBMS). A DBMS may include one or more database servers that manage access to a database. A DBMS thus functions both to efficiently store information in one or more containers and as a database server to efficiently retrieve the information therefrom, on request. Each of the data containers of a database contains records. The data within each record is organized into one or more attribute fields. Various database architectures exist.
The data containers of relational databases are tables and the records stored therein are rows. The database can contain virtually any number of tables, the attributes of which, are referred to as columns. The data containers of an object oriented database are object classes, the records of which comprise objects, which have attributes referred to as object attributes. Other database architectures also exist. For simplicity and unity herein, a relational database is discussed, solely by way of example.
To retrieve data stored in a database, queries are submitted to a database server, which computes the query and returns the data requested therewith. Query statements submitted to a database server typically conform to the syntax of a particular database language supported by the DBMS. SQL (Structured Query Language) and PL/SQL (Procedural Language/SQL) are well known examples of such languages. Other database languages, some of them proprietary, are also used. For simplicity and unity, and solely by way of example, SQL statements are discussed herein. Unless otherwise indicated, terms and phrases are used herein in contexts explained in the four paragraphs that follow, below.
Views can allow the results of some queries to be computed more efficiently. The term “view” refers to a predefined query that is treated as a table. A view is thus essentially a virtual table and may be referenced by a database statement as if the view were an actual table. Tables that actually store the data associated with a view are referred to as “base tables.” Base tables are referenced, directly or indirectly, by the predefined query. To generate the rows of a view, data is extracted and derived from the base tables. Changing data in a view's base table alters the data returned for a view.
Unless otherwise indicated, the term “Materialized View” (MV) refers to a view for which the results of the predefined query are stored as precomputed values. Materializing a materialized view obviates multiple executions of queries that may require significant time to compute. Thus, materializing a materialized view may allow faster and more efficient access to data.
Further, queries may be dynamically and transparently rewritten to reference one or more materialized views rather than base tables, which can boost database performance by allowing faster and more efficient computing of queries. Unless otherwise indicated, the term “query rewrite” herein refers to transforming a SQL query, expressed in terms of tables and views, into a statement that accesses the materialized views associated with those tables and views, instead. Inline views may be used to facilitate some query rewrites.
Information may be stored in one or more databases. When information is stored in more than one database, it can often be useful to access the information, regardless of which database it is stored in. In processing large quantities of information, speed and/or efficiency can be promoted by using and/or sharing information that is accessed from multiple databases or database instances. One or more certain database systems may function with one or more other database systems in what is referred to herein as a distributed database system. In a distributed database system, one database system may access database objects of (e.g., stored in) another database system.
In a distributed database system, an object or table may be stored in a certain database of the distributed database system that is managed by a local DBMS associated with the object or table, e.g., “local” to the certain database. Storage structures (e.g. files) that hold data for the locally stored objects or tables can be readily accessed by a database server of a local DBMS. In a distributed database system, an object or table that may not be readily accessed in this way by a certain DBMS may be stored in another database of another DBMS of the distributed database system. In such a case, the object or table is said to be “remote” to the certain DBMS and its local database. A DBMS may access a remote database object or table via another DBMS local to the remote object by, for example, issuing requests for queries to a database server of the local DBMS.
A query submitted to a DBMS of a distributed database system may refer to one or more database objects that may be stored at a remote database managed by another DBMS of the distributed database system. Queries may thus “contain” (e.g., reference) objects that are located DBMSs within the distributed database system.
In distributed database systems, queries have been rewritten with materialized views only in cases in which the materialized views contain localized database objects, i.e. the base tables of the materialized view were local objects. As a consequence, database applications that maintain tables in multiple database sites have essentially been limited in the use of query rewrite, e.g., to cases in which queries could have been rewritten using materialized views that refer to local database objects with respect to a DBMS receiving the query.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section. Similarly, issues identified with respect to one or more approaches should not assume to have been recognized in any prior art on the basis of this section, unless otherwise indicated.
Based on the foregoing, it would be useful to facilitate query rewrite using a remote object.